import pandas as pd
import glob
import os
import openpyxl
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

def dispose_screening(screening):
    df = pd.DataFrame(columns=['日期', '早上迟到时间', '早上迟到类型', '下午迟到时间', '下午迟到类型', '缺卡类型'])
    for entry in screening:
        date = entry[0]
        details = entry[1]
        morning = None
        morning_type = None
        afternoon = None
        after_type = None
        description = None
        all_descriptions = []
        
        for detail in details:
            if 'morning' in detail:
                morning = detail['morning']
                morning_type = detail['morning_type']
            elif 'afternoon' in detail:
                afternoon = detail['afternoon']
                after_type = detail['after_type']
            elif 'description' in detail:
                descriptions = detail['description']
                all_descriptions.append(descriptions)
                
         #将列表中的描述连接成一个字符串，每个描述之间用空格分隔
        description = ','.join(all_descriptions)
        
        df = df._append({
            '日期': date,
            '早上迟到时间': morning,
            '早上迟到类型': morning_type,
            '下午迟到时间': afternoon,
            '下午迟到类型': after_type,
            '缺卡类型': description
        }, ignore_index=True)
    print('=============',df)
    # 输出DataFrame
    return df



def print_xls(df,path):
    # 获取文件名和扩展名
    filename = os.path.basename(path)
    name_parts = os.path.splitext(filename)
    file_number = name_parts[0]  # 文件名部分，用于生成新的文件名

    # 生成新的Excel文件路径
    xls_file = './newXlsx/' + file_number + '.xlsx'



    # 创建一个新的Workbook对象
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = 'Sheet1'  # 修改sheet名称为Sheet1

    # 写入表头
    headers = list(df.columns)
    for col_index, header in enumerate(headers, start=1):  # openpyxl的列索引从1开始
        sheet.cell(row=1, column=col_index, value=header)
    # 写入数据行
    for row_index, row in df.iterrows():
        for col_index, value in enumerate(row, start=1):  # openpyxl的列索引从1开始
            # 根据数据类型进行格式化处理
            if isinstance(value, pd.Timestamp):
                value = value.strftime('%Y-%m-%d %H:%M:%S')  # 格式化日期
            elif isinstance(value, str) and len(value) == 10 and '-' in value:
                value = value  # 保持原样，不做处理
            elif value == '#NUM!':
                value = ''  # 可以修改为其他你希望的值，或者留空
            
            # 写入单元格
            sheet.cell(row=row_index + 2, column=col_index, value=value)
    

    # 保存文件
    workbook.save(xls_file)

    # 读取源文件
    dfs = pd.read_excel(path)

    # 加载现有的目标文件
    wb = load_workbook(xls_file)

    # 选择要写入的工作表，如果不存在则创建
    sheet_name = 'Sheet1'
    if sheet_name in wb.sheetnames:
        sheet = wb[sheet_name]
    else:
        sheet = wb.create_sheet(title=sheet_name)
    sheet.append([])  # 添加空行
    sheet.append([])  # 添加空行
    # 将DataFrame数据追加到Excel文件中，不包括列名
    for r in dataframe_to_rows(dfs, index=False, header=True):
        sheet.append(r)

    # 保存文件
    wb.save(xls_file)

def transfer_time(total_minutes):
    # 计算小时和分钟
    hours = total_minutes // 60
    minutes = total_minutes % 60

    # 如果超过60分钟，按照题主要求将分钟数设为1分钟
    if minutes > 60:
        hours += 1
        minutes = 1

    # 格式化迟到时间的字符串
    if hours > 0:
        late_time = f'{hours}小时{minutes}分钟'
    else:
        late_time = f'{minutes}分钟'

    return late_time

def screening_cards(timestamps):
    morning_work = False
    afternoon_work = False
    morning_off = False
    afternoon_off = False
    cars = []
    for timestamp in timestamps:
        time = timestamp.time()
        if datetime.strptime('05:00:00', '%H:%M:%S').time() <= time <= datetime.strptime('11:59:59', '%H:%M:%S').time():
            morning_work = True
            if morning_work and datetime.strptime('09:01:00', '%H:%M:%S').time() <= time <= datetime.strptime('12:00:00', '%H:%M:%S').time():
                target_time = datetime.strptime('09:00:00', '%H:%M:%S').time()
                late_minutes = (datetime.combine(datetime.today(), time) - datetime.combine(datetime.today(), target_time)).total_seconds() // 60
                late_time = transfer_time(late_minutes)
                cars.append({'morning': timestamp, 'morning_type': f'早上迟到时间: {late_time}'})
                print('早上迟到',morning_work)

        elif datetime.strptime('12:00:00', '%H:%M:%S').time() <= time <= datetime.strptime('13:00:00', '%H:%M:%S').time():
            morning_off = True

        elif datetime.strptime('13:00:00', '%H:%M:%S').time() <= time <= datetime.strptime('17:59:00', '%H:%M:%S').time():
            afternoon_work = True
            if datetime.strptime('13:31:00', '%H:%M:%S').time() <=  time <= datetime.strptime('16:00:00', '%H:%M:%S').time():
                target_time = datetime.strptime('13:30:00', '%H:%M:%S').time()
                late_minutes = (datetime.combine(datetime.today(), time) - datetime.combine(datetime.today(), target_time)).total_seconds() // 60
                late_time = transfer_time(late_minutes)
                cars.append({'afternoon':timestamp, 'after_type': f'下午迟到: {late_time}'})
                print('下午迟到',afternoon_work)
            if datetime.strptime('16:01:00', '%H:%M:%S').time() <=  time <= datetime.strptime('17:59:00', '%H:%M:%S').time():
                end_time = datetime.strptime('18:00:00', '%H:%M:%S').time()
                late_minutes = (datetime.combine(datetime.today(), end_time) - datetime.combine(datetime.today(), time)).total_seconds() // 60
                late_time = transfer_time(late_minutes)
                cars.append({'afternoon':timestamp, 'after_type': f'下午早退: {late_time}'})
                print('下午早退',afternoon_work)

        elif datetime.strptime('18:00:00', '%H:%M:%S').time() <= time <= datetime.strptime('23:59:59', '%H:%M:%S').time():
            afternoon_off = True




    if morning_work and afternoon_work and morning_off and afternoon_off:
        print('正常打卡')
        # cars.append('正常打卡')
    if not morning_work:
        cars.append({'description':'早上上班缺卡'})
    if not afternoon_work:
        cars.append({'description':'下午上班缺卡'})
    if not morning_off:
        cars.append({'description':'早上下班缺卡'})
    if not afternoon_off:
        cars.append({'description':'下午下班缺卡'})
    return cars


def traversal_date(path):
    workday_df = pd.read_excel('workDay.xlsx')
    workdays = workday_df['上班日期'].tolist()

    xls_data = pd.read_excel(path)

    xls_data['时间'] = pd.to_datetime(xls_data['时间'])

    screening_records = []
    for workday in workdays:
        date_records = xls_data[xls_data['时间'].dt.date == workday.date()]
    
        screening = screening_cards(date_records['时间'])
        if len(screening) != 0:
            print('-------workday--------',workday)
            print('-------screening--------',screening)
            screening_records.append([workday,screening])

    rows = dispose_screening(screening_records)
    print_xls(rows,path)

def start():
    # 指定包含 .xls 文件的文件夹路径
    folder_path = './xlsx'  # 或者 '/path/to/your/xlsx/folder/'

    # 使用 glob 模块来获取文件夹下所有的 .xls 文件路径
    xls_files = glob.glob(os.path.join(folder_path, '*.xls'))

    # 遍历获取到的每一个 .xls 文件路径
    for xls_file in xls_files:
        path=xls_file.replace('\\','/')
        traversal_date(path)


start()


